2.2 Conditional (or Logical) Aggregate Functions

COUNT Function And Its Variations

There are five variations of the COUNT function that we illustrate:

• COUNT(range) -- Counts the number of nonblank cells in a range containing numeric values.

• COUNTA(range) -- Counts the number of nonblank cells in a range. It counts both numeric and textual data.

• COUNTBLANK(range) -- Counts the number of blank cells in a range.

• COUNTIF(range, criterion) -- Counts the number of cells in a range that meet a specified criteria.

• COUNTIFS(range1, criterion1, range2, criterion2, …, range_n, criterion_n) -- Counts the number of cells in each range that meets a specified criterion for that range. Multiple ranges, each with its specified condition, are evaluated and included.

Range is the range of cells.

Criterion is a number, date, or expression that determines if a cell is counted or not. Only those cells that meet the criterion are counted. Criteria may be based on dates, numbers, and text that match specific criteria including logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. A question mark matches one character and an asterisk matches any sequence of characters. Use a tilde (~) in front of a question mark or asterisk (i.e. ~?, ~*) to find a literal question mark or asterisk. Text criteria, including math symbols, must be enclosed in double quotation marks (").

In the above figure, we illustrate four examples of the COUNT functions. The functions are actually in the A column, but are illustrated by the text in the B column. The data that we are evaluating is in the range of A2:A8.

In cell A9 is the COUNT function, which counts the numeric values of 1, 2, and 3 yielding a count of 3. In Cell A10 is the COUNTA function, which counts all nonblank cells and yields a count of 6. In Cell A11 is the COUNTBLANK function and yields a value of 1 by counting cell A8. Finally, in cell A12 is the conditional function COUNTIF. The criterion in this case tests for values less than the letter "c". The COUNTIF function tests for numbers of text values or dates depending on the type of criterion entered.

As stated previously, the criteria can be numbers, dates, or expressions. The following examples illustrate several variations of syntax:

• COUNTIF(range1,"Text1") – tests equal to "Text1" in the range.

• COUNTIF(range1, “<> Text1”) - tests not equal to in the range.

• COUNTIF(range1,A2) – tests against the value in cell A2.

• COUNTIF(range1,"<>" &A2) - tests if not equal to value in cell A2. Note the concatenation operator.

• COUNTIF(range1,">=5") - tests greater or equal to number 5

• COUNTIF(range1, ">="&A2) - tests greater or equal to value in cell A2

• COUNTIF(range1,"B*") – asterisk (*) wildcard represents any sequence of characters. Criteria are not case sensitive. Tests for any string beginning with "B".

• COUNTIF(range1,"?????") – question mark (?) represents one single character. Thus finds text containing exactly five letters

SUM Function And Its Variations

There are three versions of the SUM function. The argument list can include a either range of cells, or a comma separated list of cells.

• SUM(sum_range) - Sums the values in a sum_range.

• SUM(n1,n2, …, nx) - Sums the values in the list of cells.

• SUMIF(range, criterion, [sum_range]) - Sums the values in a sum_range that meet a criterion specified for the range. The sum_range is optional, and if ommitted the range is summed. The sum_range and range are usually distinct values, however they can also be the same values. The range and sum_range must correspond in size and direction (rows or columns).

• SUMIFS(sum_range, range1,criterion1,range2,criterion2, …, range_n,criterion_n) - Sums the values in a sum_range that meet multiple criteria. Each criterion range must each correspond to the sum_range in size and direction. In order for a cell to be included in the sum, the criteria must be met for every range.

Range - The range of cells to be evaluated with some criterion.

Criteria - The criteria used to determine which cells to add. SUMIF criteria may be based on dates, numbers, and text, and follow the same rules as the COUNT function criteria.

Sum_range - [optional] The cells to add together (if omitted, the cells in range (criteria_range) are added together). Note the sum_range argument is last in SUMIF, but the first argument in SUMIFS.

The criteria must be supplied in pairs (range/criterion) and only the first pair is required. For each additional criteria, supply an additional range/criteria pair. Up to 127 range/criterion pairs are allowed. Each additional range must have the same number of rows and columns as the sum_range.

SUMIF and SUMIFS handle ranges, but not arrays. This means functions like YEAR, which result is an array, cannot be used. For arrays use the SUMPRODUCT function.

Sadly, dates cannot take wildcards (*?) in excel in an straightforward way. One way to do it is to use the sumproduct function as seen in the following references:

Here is an example of SUMIFS with multiple ranges. In this case we are Summing a range named "amount" and testing a start date range and an end date range against dates in cells A1 and B1.

AVERAGE (arithmetic mean) function and its Variations

The AVERAGE function is essentially the combination of COUNT and SUM. It sums the total and divides by the count. Of course, AVERAGE only works on numeric data. AVERAGE is a little different than SUM because the average value depends both on the sum and on the total number of cells included in the calculation. Hence extra consideration must be made for cells that have non-numeric data. There are four separate AVERAGE functions available. AVERAGEIF, like SUMIF, can have the average_range separate from the range for the criteria.

• AVERAGE(average_range) - Finds the average of the values in average_range. The calculation ignores cells that are empty or non-numeric.

• AVERAGE(n1,n2, …, nx) - Finds the average of the values from the comma separated listof cells. The calculation ignores cells that are empty or non-numeric.

• AVERAGEA(average_range) - Finds the average of the values in a average_range, however counts cells with text or logical values giving them a value of 0. It ignores empty cells. 

• AVERAGEA(n1,n2, …, nx) Finds the average of the values in a average_range, however counts cells with text and logic with a value of 0.

• AVERAGEIF(range, criteria, [average_range]) Finds the average of the values in a average_range that meet a criteria. Average_range is optional, and if omitted, then the average of range is calculated.

• AVERAGEIFS(average_range, range1,criterion1,range2,criterion2, …, range_n,criterion_n) Finds the average of the values in a average_range that meet multiple criteria (all criteria must be satisfied).

The range and criteria arguments for AVERAGEIF are the same as SUMIF.